﻿''' <summary>
''' View Model de la  visionneuse de table
''' </summary>
''' <remarks></remarks>
Public Class ViewModelTableViewer
    Inherits ViewModelTabSectionBase

    Private _datasource As DataTable
    Private _dataview As Data.DataView
    Private _datasourcename As String
    Private _columndata As New Dictionary(Of String, ViewModelTableViewerFilter)

    ''' <summary>
    ''' Nom de la table
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property DataSourceName As String
        Get
            Return _datasourcename
        End Get
        Set(value As String)
            _datasourcename = value
            Dim cachemanager = New DataManager()
            _datasource = cachemanager.Dataset.Tables(_datasourcename)
            _dataview = New DataView(_datasource)
            OnPropertyChanged("DataView")
        End Set
    End Property

    ''' <summary>
    ''' Instance de la table
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property DataSource As DataTable
        Get
            Return _datasource
        End Get
        Set(value As DataTable)
            _datasource = value
            _datasourcename = _datasource.TableName
            _dataview = New DataView(_datasource)
            OnPropertyChanged("DataView")
        End Set
    End Property

    ''' <summary>
    ''' Vue affichée de la table
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property DataView As DataView
        Get
            Return _dataview
        End Get
    End Property

    ''' <summary>
    ''' Logique des commandes 
    ''' </summary>
    ''' <param name="commandname">Nom de la commande</param>
    ''' <remarks>Liste des commandes :
    ''' _export_table_to_excel_
    ''' _import_table_from_excel_
    ''' </remarks>
    Protected Overrides Sub ExecuteCommand(commandname As String)
        If commandname = "_export_table_to_excel_" Then

            Dim sfd = New Microsoft.Win32.SaveFileDialog()

            If sfd.ShowDialog() = True Then

                Dim info As New Common.AsynchronousOperationInfo()
                info.ShowUI = True
                info.CanReportProgres = True

                PMTModule.Application.StartAsyncOperation( _
                    Sub([Module] As Type, Data() As Object) Export_Table_To_Excel(sfd.FileName, info), info)


            End If


            Return
        End If
        If commandname = "_import_table_from_excel_" Then

            Dim ofd = New Microsoft.Win32.OpenFileDialog()
            ofd.Filter = "Fichiers EXCEL 2003 et -|*.xls|Fichiers EXCEL 2007 et +|*.xlsx;*.xlsm"


            If ofd.ShowDialog() = True Then
                Dim app As New Microsoft.Office.Interop.Excel.Application
                app.DisplayAlerts = False
                Dim wbk = app.Workbooks.Open(ofd.FileName)

                Dim q = From elt In Enumerable.Range(1, wbk.Worksheets.Count)
                        Select wbk.Worksheets(elt).Name

                Dim cbx = New Windows.Controls.ComboBox
                cbx.VerticalContentAlignment = Windows.VerticalAlignment.Center
                cbx.Height = 30

                cbx.ItemsSource = q.ToList()
                PMTModule.UIService.ShowMessage(cbx, WindowTitle:="Choix de la feuille    ")

                Dim sh As Microsoft.Office.Interop.Excel.Worksheet = wbk.Worksheets(CStr(cbx.SelectedItem))

                If sh.UsedRange.Rows.Count = 1 Then Return

                Dim srccols As New Dictionary(Of String, Integer)
                For i = 1 To sh.UsedRange.Columns.Count
                    Dim colname As String = sh.UsedRange.Columns(i).Rows(1).Value
                    If Not String.IsNullOrEmpty(colname) Then
                        If DataSource.Columns.Contains(colname) Then
                            srccols.Add(colname, i)
                        Else
                            Dim data = (From c As DataColumn In DataSource.Columns Select c.ColumnName).ToList()
                            data.Add("(Ignorer)")
                            cbx = New Windows.Controls.ComboBox
                            cbx.VerticalContentAlignment = Windows.VerticalAlignment.Center
                            cbx.Height = 30
                            cbx.ItemsSource = data
                            PMTModule.UIService.ShowMessage(cbx, WindowTitle:="Choississez la colonne correspondant à : """ & colname & """    ")
                            If cbx.SelectedItem IsNot Nothing Then
                                If Not String.IsNullOrEmpty(CStr(cbx.SelectedItem)) Then
                                    If CStr(cbx.SelectedItem) <> "(Ignorer)" Then
                                        srccols.Add(CStr(cbx.SelectedItem), i)
                                    End If
                                End If
                            End If

                        End If
                    End If
                Next



                DataSource.Rows.Clear()

                For i = 2 To sh.UsedRange.Rows.Count
                    Dim row = DataSource.NewRow()
                    For Each kvp In srccols
                        Dim v As Object = sh.UsedRange.Rows(i).Columns(kvp.Value).Value
                        If v IsNot Nothing Then
                            row(kvp.Key) = v
                        End If
                    Next
                    DataSource.Rows.Add(row)
                Next

                wbk.Close()
                app.Quit()
            End If

            Return
        End If
        If commandname = "_export_table_to_csv_" Then

            Return
        End If
        If commandname = "_import_table_from_csv_" Then

            Return
        End If


        If _columndata.ContainsKey(commandname) Then
            Dim filter = New ViewModelTableViewerFilter(Me, commandname)
            _columndata(commandname) = filter
            PMTModule.UIService.ShowMessage(filter, WindowTitle:=commandname, ShowCloseButton:=False)
        Else
            Dim filter = New ViewModelTableViewerFilter(Me, commandname)
            _columndata.Add(commandname, filter)
            PMTModule.UIService.ShowMessage(filter, WindowTitle:=commandname, ShowCloseButton:=False)
        End If
    End Sub

    Private Sub Export_Table_To_Excel(Filename As String, info As Common.AsynchronousOperationInfo)

        Dim app As New Microsoft.Office.Interop.Excel.Application
        app.DisplayAlerts = False
        Dim wbk = app.Workbooks.Add()
        Dim sh As Microsoft.Office.Interop.Excel.Worksheet = wbk.Worksheets.Add()
        sh.Name = DataSource.TableName
        app.ActiveWindow.DisplayGridlines = False

        For i = 0 To DataSource.Columns.Count - 1
            sh.Cells(1, i + 1) = DataSource.Columns(i).ColumnName
        Next

        For i = 0 To DataSource.Rows.Count - 1
            info.Message = "Ecriture de la  ligne  Numéro " & (i + 1) & " sur " & DataSource.Rows.Count
            info.Progress = Math.Max(0.0, Math.Min(1.0, CDbl(i) / CDbl(DataSource.Rows.Count - 1)))
            For j = 0 To DataSource.Columns.Count - 1
                If DataSource.Columns(j).DataType Is GetType(String) Then
                    sh.Cells(i + 2, j + 1) = "'" & CStr(DataSource.Rows(i)(j))
                Else
                    sh.Cells(i + 2, j + 1) = DataSource.Rows(i)(j)
                End If
            Next
        Next

        sh.ListObjects.AddEx( _
             SourceType:=Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, _
             Source:=sh.UsedRange, _
             XlListObjectHasHeaders:=Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, _
             TableStyleName:="TableStyleMedium5").Name = "Table_" & DataSource.TableName

        For Each sheet In wbk.Worksheets
            If sheet.Name <> sh.Name Then
                sheet.Delete()
            End If
        Next

        wbk.SaveAs(Filename:=Filename)
        app.DisplayAlerts = True

        wbk.Close()
        app.Quit()
    End Sub

    Friend Function UpdateFilter() As String
        Dim q = From elt In _columndata Select GetFilter(elt.Value)

        Dim q2 = From elt In q Where Not (String.IsNullOrEmpty(elt)) Select elt

        If q2.Count() = 0 Then Return ""

        Return String.Join(" AND ", q2.ToArray())
    End Function

    Private Function GetFilter(filter As ViewModelTableViewerFilter) As String
        If filter.Selecteds.Count = 0 Or filter.Selecteds.Count = filter.Values.Count Then
            Return ""
        End If

        Dim q = From elt In filter.Selecteds
                  Select "(" & filter.ColumnName & " = " & If(elt.GetType() Is GetType(String), "'" & elt.ToString() & "'", Convert.ToString(elt, System.Globalization.CultureInfo.InvariantCulture)) & ")"

        Return "(" & String.Join(" OR ", q.ToArray()) & ")"

    End Function

    Public Property ModeTri As Boolean
End Class
